package fr.upmc.cie.data.dbaccess;

import java.util.ArrayList;

import fr.upmc.cie.data.CategorieItem;
import fr.upmc.cie.data.ChannelItem;
import fr.upmc.cie.data.exception.ChannelNotExistException;
import fr.upmc.cie.data.exception.ChannelTitleDuplicateException;
import fr.upmc.cie.data.interfaces.IChannelDataSource;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

public class DBChannel implements IChannelDataSource{
	
	private SQLiteStatement insertStmt;
	private static final String INSERT = "insert into channel (title, url) values (?,?)";
	private SQLiteDatabase db;
	
	public DBChannel(SQLiteDatabase db) {
		this.db = db;
	}

	@Override
	public void insert(ChannelItem ci) throws ChannelTitleDuplicateException{
		insertStmt = db.compileStatement(INSERT);
		if (isChannelTitleExist(ci.getTitle())) throw new ChannelTitleDuplicateException();
		insertStmt.bindString(1, ci.getTitle());
		insertStmt.bindString(2, ci.getUrl());
		ci.setId(insertStmt.executeInsert());
	}

	@Override
	public void delete(ChannelItem ci) throws ChannelNotExistException{
		if (!isChannelExist(ci.getId())) throw new ChannelNotExistException();
		db.delete("channel", "id_channel=?", new String[] {String.valueOf(ci.getId())});
		db.delete("categorie_channel", "id_channel=?", new String[] {String.valueOf(ci.getId())});
	}

	@Override
	public void update(ChannelItem ci) throws ChannelTitleDuplicateException,ChannelNotExistException{
		if (isChannelTitleExist(ci.getTitle())&&getChannelByName(ci.getTitle()).getId()!=ci.getId()) throw new ChannelTitleDuplicateException();
		if (!isChannelExist(ci.getId())) throw new ChannelNotExistException();
		db.update("channel", ci.getContentValues(), "id_channel=?", new String[] {String.valueOf(ci.getId())});
	}

	@Override
	public ChannelItem getChannelByName(String name) throws ChannelNotExistException{
		Cursor cursor = db.query("channel", null, "title=?", new String[]{name}, null, null, null);
		
		if (cursor.moveToFirst()){
			ChannelItem ci = new ChannelItem();
			ci.setId(cursor.getLong(0));
			ci.setTitle(cursor.getString(1));
			ci.setUrl(cursor.getString(2));
			if (cursor != null && !cursor.isClosed()) {cursor.close();}
			return ci;
		}else{
			throw new ChannelNotExistException();
		}
	}

	@Override
	public ChannelItem getChannelById(Long id) throws ChannelNotExistException{
		Cursor cursor = db.query("channel", null, "id_channel=?", new String[]{String.valueOf(id)}, null, null, null);
		if (cursor.moveToFirst()){
			ChannelItem ci = new ChannelItem();
			ci.setId(cursor.getLong(0));
			ci.setTitle(cursor.getString(1));
			ci.setUrl(cursor.getString(2));
			if (cursor != null && !cursor.isClosed()) {cursor.close();}
			return ci;
		}else{
			throw new ChannelNotExistException();
		}
	}
	
	@Override
	public ArrayList<ChannelItem> getAllChannel(){
		Cursor cursor = db.query("channel", null, null, null, null, null, null);
		ArrayList<ChannelItem> cl = new ArrayList<ChannelItem>();
		if (cursor.moveToFirst()){
			do{
				ChannelItem ci = new ChannelItem();
				ci.setId(cursor.getLong(0));
				ci.setTitle(cursor.getString(1));
				ci.setUrl(cursor.getString(2));
				cl.add(ci);
			}while(cursor.moveToNext());
			if (cursor != null && !cursor.isClosed()) {cursor.close();}
		}
		return cl;
	}
	

	@Override
	public void setChannelCategorie(ChannelItem ci, CategorieItem cti) {
		db.delete("categorie_channel", "id_channel=?", new String[] {String.valueOf(ci.getId())});
		SQLiteStatement insertRealationStmt;
		String INSERT = "insert into categorie_channel (id_categorie, id_channel) values (?,?)";
		insertRealationStmt=db.compileStatement(INSERT);
		insertRealationStmt.bindLong(1, cti.getId());
		insertRealationStmt.bindLong(2, ci.getId());
		insertRealationStmt.executeInsert();
	}
	
	/**
	 * Tester si le nom existe
	 * @param title
	 * @return
	 */
	private boolean isChannelTitleExist(String title){
		Cursor cursor = db.query("channel", null, "title=?", new String[]{title}, null, null, null);
		if (!cursor.moveToFirst()){
			return false;
		}else{
			if (!cursor.isClosed()) cursor.close();
			return true;
		}
	}
	
	/**
	 * Tester si channel existe
	 * @param id
	 * @return
	 */
	private boolean isChannelExist(Long id){
		Cursor cursor = db.query("channel", null, "id_channel=?", new String[]{String.valueOf(id)}, null, null, null);
		if (!cursor.moveToFirst()){
			return false;
		}else{
			if (!cursor.isClosed()) cursor.close();
			return true;
		}
	}

	@Override
	public long getChannelCategorieId(ChannelItem c) {
		Cursor cursor = db.query("categorie_channel", new String[]{"id_categorie"}, "id_channel=?", new String[]{String.valueOf(c.getId())}, null, null, null);
		if (!cursor.moveToFirst()){
			return 0;
		}else{
			long  res = cursor.getLong(0);
			if (!cursor.isClosed()) cursor.close();
			return res;
		}
	}


}
